package br.com.dividas;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.flex.remoting.RemotingDestination;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Service;


@Service
@RemotingDestination
public class Servicodividas {



@Autowired
private JdbcTemplate jdbc;

@Autowired
private NamedParameterJdbcTemplate namParameterJdbcTemplate;


public List getlistadividas(int CCP){
	return jdbc.queryForList("SELECT DUAM.\"DUAM\", DUAM_IT.\"PARCELA\", DUAM_IT.\"VL_DIVIDA\" AS DIVIDA, 	DUAM_IT.\"DATA_VENC\", 	TIPOAVIS.\"DS_TIPOAVI\" AS RECEITA,DUAM.\"CCP\"	" +
			"FROM \"SCH\".\"DUAM\" DUAM " +
			"JOIN \"SCH\".\"PESSOA\" PESSOA ON DUAM.\"CCP\"= PESSOA.\"CCP\"" +
			"JOIN \"SCH\".\"DUAM_IT\" DUAM_IT ON DUAM.\"DUAM\" = DUAM_IT.\"DUAM\" " +
			"JOIN \"SCH\".\"TIPOAVIS\" TIPOAVIS ON DUAM.\"REC\" = TIPOAVIS.\"CD_TIPOAVI\"" +
			"WHERE DUAM.\"FLAG_PG_TOTAL\" = 0 AND (DUAM.\"PGTO_PARC\"::text = '0'::text	AND DUAM_IT.\"PARCELA\" = 0 OR DUAM.\"PGTO_PARC\"::text > '0'::text AND DUAM_IT.\"PARCELA\" > 0)" +
			"AND DUAM_IT.\"AVISO\" <= 0 AND DUAM_IT.\"VL_DIVIDA\" <> 0 AND DUAM.\"VL_DIVIDA\" <> 0 	AND DUAM.\"CCP\" = "+CCP+"");
}
public List getlistatotal(int CCP) {
	return jdbc.queryForList("SELECT DUAM.\"CCP\",SUM(DUAM_IT.\"VL_DIVIDA\") AS TOTAL_DIVIDA " +
			"FROM \"SCH\".\"DUAM\" DUAM " +
			"JOIN \"SCH\".\"PESSOA\" PESSOA ON DUAM.\"CCP\" = PESSOA.\"CCP\" " +
			"JOIN \"SCH\".\"DUAM_IT\" DUAM_IT ON DUAM.\"DUAM\" = DUAM_IT.\"DUAM\" " +
			"JOIN \"SCH\".\"TIPOAVIS\" TIPOAVIS ON DUAM.\"REC\" = TIPOAVIS.\"CD_TIPOAVI\" " +
			"WHERE DUAM.\"FLAG_PG_TOTAL\" = 0 AND (DUAM.\"PGTO_PARC\"::text = '0'::text AND DUAM_IT.\"PARCELA\" = 0 OR DUAM.\"PGTO_PARC\"::text > '0'::text AND DUAM_IT.\"PARCELA\" > 0) " +
			"AND DUAM_IT.\"AVISO\" <= 0 AND DUAM_IT.\"VL_DIVIDA\" <> 0 AND DUAM.\"VL_DIVIDA\" <> 0 AND DUAM.\"CCP\" = "+CCP+" GROUP BY DUAM.\"CCP\"");
}

}